﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Wwtfly.BBS.BackModels.Models.Plays;
using System.Data;
using Wwtfly.DBUtility;
using Wwtfly.BBS.Framework.Common;
using System.Data.SqlClient;

namespace Wwtfly.BBS.BackDAL.Plays
{
    public  class PlaysDAL
    {

        /// <summary>
        /// 分页获取 彩种信息  sql语句
        /// </summary>
        private static readonly string GetPlaysShowsInfoPageList_Select = "proc_GetPlaysShowsInfoPageList_Select";

        public const string PM_PLAYS_SELECT_BY_ID = " SELECT [Id],[CreateTime],[LotteryID],[PlayNo],[PlayName],[StartTime],[EndTime],[WinLotteryNumber],[StateUpdatetime],[PlayState]" +
                                        " FROM [pm_plays]" +
                                        " WHERE (" +
                                        " [Id] = @Id)";

        public const string PM_PLAYS_SELECT = " SELECT [Id],[CreateTime],[LotteryID],[PlayNo],[PlayName],[StartTime],[EndTime],[WinLotteryNumber],[StateUpdatetime],[PlayState]" +
                                            " FROM [pm_plays] {0}";



        public const string PM_PLAYS_INSERT = " INSERT INTO [pm_plays] (" +
                                            "[CreateTime],[LotteryID],[PlayNo],[PlayName],[StartTime],[EndTime],[WinLotteryNumber],[StateUpdatetime],[PlayState]" +
                                        ") VALUES (" +
                                            "@CreateTime,@LotteryID,@PlayNo,@PlayName,@StartTime,@EndTime,@WinLotteryNumber,@StateUpdatetime,@PlayState" +
                                        ");select @Id = @@Identity;";


        public const string PM_PLAYS_UPDATE = " UPDATE [pm_plays] SET " +
                                            "[CreateTime]=@CreateTime,[LotteryID]=@LotteryID,[PlayNo]=@PlayNo,[PlayName]=@PlayName,[StartTime]=@StartTime,[EndTime]=@EndTime,[WinLotteryNumber]=@WinLotteryNumber,[StateUpdatetime]=@StateUpdatetime,[PlayState]=@PlayState" +
                                        " WHERE " +
                                            " [Id] = @Id";



        public const string PM_PLAYS_DELETE = "DELETE FROM [pm_plays]" +
                                        " WHERE " +
                                            " [Id] = @Id";


        public const string PM_PLAYS_SELECT_LotteryID = "select [Id], [CreateTime], [LotteryID], [PlayNo], [PlayName], [StartTime], [EndTime], [WinLotteryNumber], [StateUpdatetime], [PlayState] from pm_plays where [LotteryID]=@LotteryID and [PlayState]=1 and EndTime>GETDATE()";

        public const string PM_PLAYS_SELECT_BY_LotteryID_PlayNo = " SELECT [Id],[CreateTime],[LotteryID],[PlayNo],[PlayName],[StartTime],[EndTime],[WinLotteryNumber],[StateUpdatetime],[PlayState]" +
                                        " FROM [pm_plays]" +
                                        " WHERE (" +
                                        " [LotteryID] = @LotteryID and [PlayNo]=@PlayNo)";
        public const string PM_PLAYS_LotteryWinnumber = "select Id, CreateTime, LotteryID, PlayNo, PlayName, StartTime, EndTime, WinLotteryNumber, StateUpdatetime, PlayState from viewPlayWinLotteryNumber where rnsum=1 order by LotteryID desc";

        public const string Select_SSQ_DX = "P_TrendChart_SSQ_DX";
        public const string Select_SSQ_ZHFB = "P_TrendChart_SSQ_HMFB";
        public const string Select_3D_ZHFB = "P_TrendChart_3D_ZHFB";
        public const string Select_CJDLT_HMFB = "P_TrendChart_CJDLT_HMFB";
        public const string Select_7X_HMFB = "P_TrendChart_7X_HMFB";
        public const string Select_7LC_HMFB = "P_TrendChart_7CL_HMFB";

        public const string Select_PL3_HMFB = "P_TrendChart_PL3_HMFB";
        public const string Select_PL5_HMFB = "P_TrendChart_PL5_HMFB";
        public const string Select_22X5_HMFB = "P_TrendChart_22X5_HMFB";
        /// <summary>
        /// 获取22选5综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select_22X5_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_22X5_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }
        /// <summary>
        /// 获取排列五综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select_PL5_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_PL5_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }


        /// <summary>
        /// 获取排列三综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select_PL3_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_PL3_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        /// <summary>
        /// 获取七乐彩综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select_7LC_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_7LC_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }
        /// <summary>
        /// 获取7星综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select_7X_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_7X_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }
        /// <summary>
        /// 获取大乐透综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectCJDLT_HMFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_CJDLT_HMFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;

        }
        /// <summary>
        /// 获取双色球综合分布图
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectSSQZHFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_SSQ_ZHFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;

        } 
        /// <summary>
        /// 获取双色球历史数据分析图
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectSSQdxListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_SSQ_DX);
           DataSet ds= db.ExecuteDataSet(dbCommand);
           return ds.Tables.Count>0?ds.Tables[0]:null;

        } 
        /// <summary>
        /// 获取3D历史数据分析图
        /// </summary>
        /// <returns></returns>
        public static DataTable Select3DZHFBListWinber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(Select_3D_ZHFB);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;

        }

        /// <summary>
        /// 查询首页的彩种开奖信息
        /// </summary>
        /// <returns></returns>
        public static IList<PlaysShowsModel> GetPlayLotteryWinNumber()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_LotteryWinnumber);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                return Emit.ConvertToList<PlaysShowsModel>(dr);
            }
        }
        /// <summary>
        /// 根据彩种ID获取期号相关信息
        /// </summary>
        /// <param name="LotteryId"></param>
        /// <returns></returns>
        public static IList<PlaysShowsModel> GetPlayShowsList(int LotteryId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_SELECT_LotteryID);
            db.AddInParameter(dbCommand, "@LotteryID", DbType.Int32, LotteryId);
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                return Emit.ConvertToList<PlaysShowsModel>(dr);
            }
        }

        /// <summary>
        /// 分页 获取新闻 的相关信息
        /// </summary>
        /// <param name="pageParams">分页参数</param>
        /// <param name="searchModel">新闻搜索 实体类</param>
        /// <returns>分页后的结果</returns>
        public static PagedList<PlaysShowsModel>
            GetPlaysShowsPageList(PageParams pageParams, PlaysShowsSearchModel searchModel)
        {

            IList<PlaysShowsModel> list = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(GetPlaysShowsInfoPageList_Select);
            DBParamsUtility.CreatePageParamsOfNotOutTotalCount(pageParams, db, dbCommand);
            dbCommand.Parameters.Add(new SqlParameter() { ParameterName = "@LotteryID", SqlDbType = SqlDbType.Int, Value = searchModel.LotteryID });
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                list = Emit.ConvertToList<PlaysShowsModel>(dr);
                pageParams.SetTotalCount((int)DBParamsUtility.GetTotalCount<PlaysShowsModel>(list));
            }
            return list.ToPagedList<PlaysShowsModel>(pageParams);
        }
        /// <summary>
        /// 根据Id获取指定的pmplays
        /// </summary>
        /// <param name="Id">pm_plays主键</param>
        /// <returns></returns>
        public static PlaysShowsModel GetInfo(Int32 Id)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_SELECT_BY_ID);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, Id);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                    return Emit.ConvertTo<PlaysShowsModel>(dr);
            }
        }
        /// <summary>
        /// 根据彩种类型及期号获取指定的pmplays
        /// </summary>
        /// <returns></returns>
        public static PlaysShowsModel GetInfo(int LotteryID,string PlayNo)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_SELECT_BY_LotteryID_PlayNo);
            db.AddInParameter(dbCommand, "@LotteryID", DbType.Int32, LotteryID);
            db.AddInParameter(dbCommand, "@PlayNo", DbType.String, PlayNo);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                return Emit.ConvertTo<PlaysShowsModel>(dr);
            }
        }

        /// <summary>
        /// 添加新的pmplays
        /// </summary>
        /// <param name="pmplays">pmplays实体</param>
        /// <returns></returns>
        public static int Add(PlaysShowsModel pmplays)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_INSERT);

            db.AddOutParameter(dbCommand, "@Id", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.CreateTime));
            db.AddInParameter(dbCommand, "@LotteryID", DbType.Int32, DBConvert.ToDBValue(pmplays.LotteryID));
            db.AddInParameter(dbCommand, "@PlayNo", DbType.String, DBConvert.ToDBValue(pmplays.PlayNo));
            db.AddInParameter(dbCommand, "@PlayName", DbType.String, DBConvert.ToDBValue(pmplays.PlayName));
            db.AddInParameter(dbCommand, "@StartTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.StartTime));
            db.AddInParameter(dbCommand, "@EndTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.EndTime));
            db.AddInParameter(dbCommand, "@WinLotteryNumber", DbType.String, DBConvert.ToDBValue(pmplays.WinLotteryNumber));
            db.AddInParameter(dbCommand, "@StateUpdatetime", DbType.DateTime, DBConvert.ToDBValue(pmplays.StateUpdatetime));
            db.AddInParameter(dbCommand, "@PlayState", DbType.Int16, DBConvert.ToDBValue(pmplays.PlayState));


            int count = db.ExecuteNonQuery(dbCommand);

            if (count > 0)
                return (int)dbCommand.Parameters["@Id"].Value;
            return -1;
        }

        /// <summary>
        /// 修改WKF_WorkItemOfKit
        /// </summary>
        /// <param name="pmplays">pmplays实体</param>
        /// <returns></returns>
        public static bool Update(PlaysShowsModel pmplays)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_UPDATE);

            db.AddInParameter(dbCommand, "@Id", DbType.Decimal, DBConvert.ToDBValue(pmplays.Id));
            db.AddInParameter(dbCommand, "@CreateTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.CreateTime));
            db.AddInParameter(dbCommand, "@LotteryID", DbType.Int32, DBConvert.ToDBValue(pmplays.LotteryID));
            db.AddInParameter(dbCommand, "@PlayNo", DbType.String, DBConvert.ToDBValue(pmplays.PlayNo));
            db.AddInParameter(dbCommand, "@PlayName", DbType.String, DBConvert.ToDBValue(pmplays.PlayName));
            db.AddInParameter(dbCommand, "@StartTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.StartTime));
            db.AddInParameter(dbCommand, "@EndTime", DbType.DateTime, DBConvert.ToDBValue(pmplays.EndTime));
            db.AddInParameter(dbCommand, "@WinLotteryNumber", DbType.String, DBConvert.ToDBValue(pmplays.WinLotteryNumber));
            db.AddInParameter(dbCommand, "@StateUpdatetime", DbType.DateTime, DBConvert.ToDBValue(pmplays.StateUpdatetime));
            db.AddInParameter(dbCommand, "@PlayState", DbType.Int16, DBConvert.ToDBValue(pmplays.PlayState));


            return db.ExecuteNonQuery(dbCommand) > 0;
        }

        /// <summary>
        /// 删除WKF_WorkItemOfKit 
        /// </summary>
        /// <param name="Id">pm_plays主键</param>
        /// <returns></returns>
        public static bool Delete(Int32 Id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(PM_PLAYS_DELETE);
            db.AddInParameter(dbCommand, "@Id", DbType.Int32, Id);
            return db.ExecuteNonQuery(dbCommand) > 0;
        }

    }
}
